﻿CREATE SCHEMA pca_almacen;

CREATE TABLE pca_almacen.tipos_articulo
(
	tipo_articulo_id integer NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT tipos_articulo_pk PRIMARY KEY(tipo_articulo_id)
);

--Insertamos los datos de inicio
DELETE FROM pca_almacen.tipos_articulo;
INSERT INTO pca_almacen.tipos_articulo VALUES(0,'Articulo');
INSERT INTO pca_almacen.tipos_articulo VALUES(1,'Servicio');


-- Familias de productos
CREATE TABLE pca_almacen.familias
(
	nombre_familia varchar(50) NOT NULL,
	nombre_sub_familia varchar(50) NOT NULL,
	CONSTRAINT grupos_pk PRIMARY KEY(nombre_familia,nombre_sub_familia)
);

-- Marcas
CREATE TABLE pca_almacen.marcas
(
	nombre_marca varchar(50) NOT NULL,
	CONSTRAINT marcas_pk PRIMARY KEY(nombre_marca)
);

-- Articulos: cada articulo es unico incluso si articulos con mismo nombre
CREATE SEQUENCE pca_almacen.seq_articulos_id;
CREATE TABLE pca_almacen.articulos
(
	articulo_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_almacen.seq_articulos_id')::integer),
	nombre_familia varchar(50) NOT NULL,
	nombre_sub_familia varchar(50) NOT NULL,
	nombre_marca varchar(50),
	cod_interno varchar(50) NOT NULL,
	cod_barras text NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(currval('pca_almacen.seq_articulos_id')::integer),
	nombre_corto varchar(300) NOT NULL,
	descripcion text,
	keywords text, --palabras claves que identifican al articulo cuando se requiere buscarlo
	fecha_registro timestamp with time zone NOT NULL DEFAULT current_timestamp,
	activo boolean NOT NULL DEFAULT true,--indica si esta activo o no el articulo en el sistema
	tipo_articulo_id integer NOT NULL,--0 para articulo 1 para servicio
	CONSTRAINT articulos_pk PRIMARY KEY(articulo_id),
	CONSTRAINT familias_fkey FOREIGN KEY(nombre_familia,nombre_sub_familia)
		REFERENCES pca_almacen.familias(nombre_familia,nombre_sub_familia) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT marcas_fkey FOREIGN KEY(nombre_marca)
		REFERENCES pca_almacen.marcas(nombre_marca) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT tipo_articulo_fkey FOREIGN KEY(tipo_articulo_id)
		REFERENCES pca_almacen.tipos_articulo(tipo_articulo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT cod_interno_key UNIQUE(cod_interno),
	CONSTRAINT cod_barras_key UNIQUE(cod_barras)
);

CREATE SEQUENCE pca_almacen.seq_fotos_id;
CREATE TABLE pca_almacen.fotos
(
	foto_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_almacen.seq_fotos_id')::integer),
	articulo_id bigint NOT NULL,
	nombre varchar(50) NOT NULL,
	ruta text NOT NULL,
	fecha_registro timestamp with time zone NOT NULL DEFAULT current_timestamp,
	predeterminado boolean NOT NULL DEFAULT false,--valor que indica si la foto es la predeterminada para mostrar en el catalogo
	CONSTRAINT fotos_pk PRIMARY KEY(foto_id),
	CONSTRAINT articulos_fkey FOREIGN KEY(articulo_id)
		REFERENCES pca_almacen.articulos(articulo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT nombre_key UNIQUE(articulo_id,nombre)
);

CREATE TABLE pca_almacen.almacenes
(
	almacen varchar(100) NOT NULL,
	ubicacion varchar(100) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	CONSTRAINT almacenes_pk PRIMARY KEY(almacen,ubicacion,oficina_id,empresa_id),
	CONSTRAINT oficinas_fkey FOREIGN KEY(oficina_id,empresa_id)
		REFERENCES pca.oficinas(oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE TABLE pca_almacen.inventario
(
	articulo_id bigint NOT NULL,
	almacen varchar(100) NOT NULL,
	ubicacion varchar(100) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	sesion_id bigint NOT NULL,
	medida_id varchar(2) NOT NULL,--la unidad de medida en la que esta el articulo
	stock_min numeric(11,3) NOT NULL,--el stock minimo antes de mostrar alertas
	stock_max numeric(11,3) NOT NULL,--el stock maximo antes de mostrar alertas
	stock_ini numeric(11,3) NOT NULL,--el stock inicial cuando se realizo el inventario
	stock_fin numeric(11,3) NOT NULL,--el stock actual
	stock_reservado numeric(11,3) NOT NULL DEFAULT 0,--la suma total de stock que se estan separando
	moneda_id_costo varchar(5) NOT NULL,
	incluye_igv_precio_costo boolean NOT NULL,
	precio_costo numeric(11,3) NOT NULL,
	precio_costo_prom numeric(11,3) NOT NULL,
	moneda_id_venta varchar(5) NOT NULL,
	incluye_igv_precio_venta boolean NOT NULL,
	precio_venta numeric(11,3) NOT NULL,
	CONSTRAINT articulos_almacenes_stock_pk PRIMARY KEY(articulo_id,almacen,ubicacion,oficina_id,empresa_id),
	CONSTRAINT articulos_fkey FOREIGN KEY(articulo_id)
		REFERENCES pca_almacen.articulos(articulo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT almacenes_fkey FOREIGN KEY(almacen,ubicacion,oficina_id,empresa_id)
		REFERENCES pca_almacen.almacenes(almacen,ubicacion,oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT medidas_fkey FOREIGN KEY(medida_id)
		REFERENCES pca_tablas.medidas(medida_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT monedas_costo_fkey FOREIGN KEY(moneda_id_costo)
		REFERENCES pca_tablas.monedas(moneda_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT monedas_venta_fkey FOREIGN KEY(moneda_id_venta)
		REFERENCES pca_tablas.monedas(moneda_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE SEQUENCE pca_almacen.seq_articulo_stock_reservado_id;
CREATE TABLE pca_almacen.articulos_stock_reservado
(
	articulo_stock_reservado_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_almacen.seq_articulo_stock_reservado_id')::integer),
	sesion_id bigint NOT NULL,
	articulo_id bigint NOT NULL,
	stock_reservado numeric(11,3) NOT NULL,
	CONSTRAINT articulos_stock_reservado_pk PRIMARY KEY(articulo_stock_reservado_id),
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT articulos_fkey FOREIGN KEY(articulo_id)
		REFERENCES pca_almacen.articulos(articulo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE SEQUENCE pca_almacen.seq_precios_his_id;
CREATE TABLE pca_almacen.precios_his
(
	precio_his_id integer NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_almacen.seq_precios_his_id')::integer),
	articulo_id bigint NOT NULL,
	almacen varchar(100) NOT NULL,
	ubicacion varchar(100) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	moneda_id_costo varchar(5) NOT NULL,
	incluye_igv_precio_costo boolean NOT NULL,
	precio_costo numeric(11,3) NOT NULL,
	precio_costo_prom numeric(11,3) NOT NULL,
	moneda_id_venta varchar(5) NOT NULL,
	incluye_igv_precio_venta boolean NOT NULL,
	precio_venta numeric(11,3) NOT NULL,
	fecha_hora timestamp with time zone NOT NULL DEFAULT current_timestamp,--fecha y hora en la que se registro el precio historico
	CONSTRAINT precios_his_pk PRIMARY KEY(precio_his_id),
	CONSTRAINT articulos_fkey FOREIGN KEY(articulo_id)
		REFERENCES pca_almacen.articulos(articulo_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT almacenes_fkey FOREIGN KEY(almacen,ubicacion,oficina_id,empresa_id)
		REFERENCES pca_almacen.almacenes(almacen,ubicacion,oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT monedas_costo_fkey FOREIGN KEY(moneda_id_costo)
		REFERENCES pca_tablas.monedas(moneda_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT monedas_venta_fkey FOREIGN KEY(moneda_id_venta)
		REFERENCES pca_tablas.monedas(moneda_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

-----------------------------------------------------------------------------------------------
------------------------------------------ Vistas ---------------------------------------------
-----------------------------------------------------------------------------------------------

--Fucion de la tabla inventario con articulos
CREATE OR REPLACE VIEW pca_almacen.vw_inventario AS
SELECT
	i.articulo_id,
	a.nombre_familia,
	a.nombre_sub_familia,
	a.nombre_marca,
	a.cod_interno,
	a.cod_barras,
	a.nombre_corto,
	a.descripcion,
	a.keywords,
	a.fecha_registro,
	a.activo,
	a.tipo_articulo_id,
	i.almacen,
	i.ubicacion,
	i.oficina_id,
	i.empresa_id,
	i.sesion_id,
	i.medida_id,
	i.stock_min,
	i.stock_max,
	i.stock_ini,
	i.stock_fin,
	i.stock_reservado,
	i.moneda_id_costo,
	mc.nombre AS moneda_costo,
	mc.siglas AS moneda_siglas_costo,
	i.incluye_igv_precio_costo,
	i.precio_costo,
	i.precio_costo_prom,
	i.moneda_id_venta,
	mv.nombre AS moneda_venta,
	mv.siglas AS moneda_siglas_venta,
	i.incluye_igv_precio_venta,
	i.precio_venta
FROM
	pca_almacen.inventario i
INNER JOIN
	pca_almacen.articulos a
ON
	(i.articulo_id=a.articulo_id)
INNER JOIN
	pca_tablas.monedas mc --moneda costo
ON
	(i.moneda_id_costo=mc.moneda_id)
INNER JOIN
	pca_tablas.monedas mv --moneda venta
ON
	(i.moneda_id_venta=mv.moneda_id);
	
-----------------------------------------------------------------------------------------------
----------------------------------------- Trigger ---------------------------------------------
-----------------------------------------------------------------------------------------------

-- Guarda los precios historicos de los productos
CREATE OR REPLACE FUNCTION pca_almacen.fn_registra_precio_his() RETURNS TRIGGER AS
$$
BEGIN

     IF(TG_OP='UPDATE' AND ((OLD.moneda_id_costo <> NEW.moneda_id_costo) OR
	(OLD.incluye_igv_precio_costo <> NEW.incluye_igv_precio_costo) OR
	(OLD.precio_costo <> NEW.precio_costo) OR
	(OLD.precio_costo_prom <> NEW.precio_costo_prom) OR
	(OLD.moneda_id_venta <> NEW.moneda_id_venta) OR
	(OLD.incluye_igv_precio_venta <> NEW.incluye_igv_precio_venta) OR
	(OLD.precio_venta <> NEW.precio_venta)))THEN
	
	INSERT INTO pca_almacen.precios_his
		(articulo_id,
		almacen,
		ubicacion,
		oficina_id,
		empresa_id,
		moneda_id_costo,
		incluye_igv_precio_costo,
		precio_costo,
		precio_costo_prom,
		moneda_id_venta,
		incluye_igv_precio_venta,
		precio_venta)
	VALUES 
		(OLD.articulo_id,
		OLD.almacen,
		OLD.ubicacion,
		OLD.oficina_id,
		OLD.empresa_id,
		OLD.moneda_id_costo,
		OLD.incluye_igv_precio_costo,
		OLD.precio_costo,
		OLD.precio_costo_prom,
		OLD.moneda_id_venta,
		OLD.incluye_igv_precio_venta,
		OLD.precio_venta);	
     END IF;
     RETURN NULL;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_registra_precio_his ON pca_almacen.inventario;

CREATE TRIGGER tri_registra_precio_his AFTER UPDATE ON pca_almacen.inventario
FOR EACH ROW
EXECUTE PROCEDURE pca_almacen.fn_registra_precio_his();

-- Eliminar las tablas relacionadas con la tabla almacenes
CREATE OR REPLACE FUNCTION pca_almacen.fn_drop_references_almacenes() RETURNS TRIGGER AS
$$
BEGIN
	IF(TG_OP='DELETE')THEN
		DELETE FROM 
			pca_almacen.precios_his
		WHERE
			almacen=OLD.almacen AND
			ubicacion=OLD.ubicacion AND
			oficina_id=OLD.oficina_id AND
			empresa_id=OLD.empresa_id;
		RETURN OLD;
	END IF;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_drop_references_almacenes ON pca_almacen.almacenes;

CREATE TRIGGER tri_drop_references_almacenes BEFORE DELETE ON pca_almacen.almacenes
FOR EACH ROW
EXECUTE PROCEDURE pca_almacen.fn_drop_references_almacenes();

-- Eliminar las tablas relacionadas con la tabla inventario para poder hacer un DELETE directamente
CREATE OR REPLACE FUNCTION pca_almacen.fn_drop_references_articulo() RETURNS TRIGGER AS
$$
BEGIN
  IF(TG_OP='DELETE') THEN
       DELETE FROM pca_almacen.articulos_stock_reservado WHERE articulo_id=OLD.articulo_id;
       RETURN OLD;
  END IF;
END;
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_drop_references_articulo ON pca_almacen.inventario;

CREATE TRIGGER tri_drop_references_articulo BEFORE DELETE ON pca_almacen.inventario
FOR EACH ROW
EXECUTE PROCEDURE pca_almacen.fn_drop_references_articulo();

--Cuenta el stock reservado
CREATE OR REPLACE FUNCTION pca_almacen.fn_update_stock_reservado()RETURNS trigger AS
$$
BEGIN
	IF(TG_OP='INSERT')THEN
		UPDATE pca_almacen.inventario SET
			stock_reservado=stock_reservado+NEW.stock_reservado
		WHERE
			articulo_id=NEW.articulo_id;
	ELSIF(TG_OP='DELETE')THEN
		UPDATE pca_almacen.inventario SET
			stock_reservado=stock_reservado-OLD.stock_reservado
		WHERE
			articulo_id=OLD.articulo_id;
	END IF;
END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tri_update_stock_reservado ON pca_almacen.articulos_stock_reservado;

CREATE TRIGGER tri_update_stock_reservado AFTER INSERT OR DELETE ON pca_almacen.articulos_stock_reservado
FOR EACH ROW
EXECUTE PROCEDURE pca_almacen.fn_update_stock_reservado();

